﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TNO.Education.Helpers
{
    public class DataHelper
    {
        protected static string ConnectionString = System.Configuration.ConfigurationManager.
                ConnectionStrings["TuyenSinhConnectionString"].ConnectionString;

        /// <summary>
        /// Get Diem Chuan
        /// </summary>
        /// <returns></returns>
        public static DataTable GetStdUniversity(int year)
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT RTRIM(ID) AS ID, Title FROM TruongDaiHoc 
                WHERE RTRIM(ID) IN (SELECT DISTINCT ID_Truong FROM DiemChuan
                                    WHERE Nam = @Year)
                ORDER BY Title";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        cmd.Parameters.AddWithValue("@Year", year);
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Khoi Thi
        /// </summary>
        /// <returns></returns>
        public static DataTable GetBlocks()
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT RTRIM(ID_Khoi) AS ID_Khoi, TenKhoi FROM KhoiThi
                ORDER BY ID_Khoi";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Diem Chuan
        /// </summary>
        /// <returns></returns>
        public static DataTable GetStdScore(string universityId, string blockId, int year)
        {
            DataTable table = new DataTable();
//            string sqlQuery = @"
//                SELECT t3.MaNganh, t3.TenNganh, t1.Diem, t1.Note,
//                   REPLACE((SELECT KhoiThi AS [data()]
//                       FROM DiemChuan t2
//                      WHERE t2.ID_Truong = t1.ID_Truong 
//                      AND (t1.ID_Nganh IS NULL OR t2.ID_Nganh = t1.ID_Nganh)
//                      ORDER BY KhoiThi
//                        FOR XML PATH('')
//                        ), ' ', ', ') AS Khoi
//                  FROM DiemChuan t1
//                  LEFT JOIN Nganh t3
//                  ON t1.ID_Nganh = t3.ID_Nganh
//                  WHERE Nam = @Year
//                    AND t1.ID_Truong = @ID_Truong
//                    AND (@KhoiThi IS NULL OR t1.KhoiThi = @KhoiThi)
//                  GROUP BY t1.ID_Truong, t1.ID_Nganh, t3.MaNganh, t3.TenNganh, t1.Diem, t1.Note
//                  ORDER BY t1.OrderIndex;";

            string sqlQuery = @"
                            SELECT MaNganh, TenNganh, KhoiThi, Diem, Note
                              FROM DiemChuan
                              WHERE Nam = @Year
                                AND ID_Truong = @ID_Truong
                                AND (@KhoiThi IS NULL OR (', ' + KhoiThi + ',') LIKE @KhoiThi)
                              ORDER BY OrderIndex;";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        cmd.Parameters.AddWithValue("@Year", year);
                        cmd.Parameters.AddWithValue("@ID_Truong", universityId);
                        cmd.Parameters.AddWithValue("@KhoiThi", blockId == string.Empty ? DBNull.Value : (object)("%, " + blockId + ",%"));
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Danh Sach Tat Ca Truong DH-CD
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllUniversity()
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT RTRIM(ID) AS ID, Title FROM TruongDaiHoc 
                WHERE RTRIM(ID) IN (SELECT DISTINCT ID_Truong FROM ThiSinh_DaiHoc)
                ORDER BY Title";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Top New Danh Sach Truong DH-CD
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTopNewUniversity(int topCount)
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT TOP " + topCount + @" RTRIM(ID) AS ID, Title FROM TruongDaiHoc
                WHERE RTRIM(ID) IN (SELECT DISTINCT ID_Truong FROM ThiSinh_DaiHoc)
                ORDER BY CreatedDate DESC, Title";

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }
        
        /// <summary>
        /// Get Top New Danh Sach Truong DH-CD Moi Co Diem Chuan
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTopNewStdUniversity(int topCount)
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT TOP " + topCount + @" * FROM (
                  SELECT DISTINCT ID_Truong AS ID, B.Title, MAX(A.CreatedDate) AS CreatedDate FROM DiemChuan A
                  LEFT JOIN TruongDaiHoc B
                  ON A.ID_Truong = RTRIM(B.ID)
                  GROUP BY ID_Truong, B.Title) C
                  ORDER BY CreatedDate DESC, Title";

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Danh Sach Ket Qua Thi
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="university"></param>
        /// <param name="searchEndWith"></param>
        /// <returns></returns>
        public static DataTable GetResults(string keyword, string university, bool searchEndWith)
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, *
                            FROM ThiSinh_DaiHoc
                            WHERE ID_Truong = @University
                            AND SBD = @SBD";
            float code;
            bool isSearchByCode = float.TryParse(keyword, out code);
            if (!isSearchByCode)
            {
                sqlQuery = @"
                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, *
                            FROM ThiSinh_DaiHoc
                            WHERE ID_Truong = @University";
                if (searchEndWith)
                {
                    sqlQuery += " AND HoTen LIKE @Name";
                }
                else
                {
                    sqlQuery += " AND HoTen = @Name";
                }
            }

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        cmd.Parameters.AddWithValue("@University", university);
                        if (isSearchByCode)
                            cmd.Parameters.AddWithValue("@SBD", code);
                        else if (searchEndWith)
                        {
                            cmd.Parameters.AddWithValue("@Name", '%' + keyword);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Name", keyword);
                        }

                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return table;
        }

        /// <summary>
        /// Get Thong Tin Thi Sinh
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="totalPages"></param>
        /// <returns></returns>
        public static StudentInfo GetStudentInfo(int id, int pageSize, out int currentPage, out int totalPages)
        {
            currentPage = 0;
            totalPages = 0;

            StudentInfo student = new StudentInfo();
            student.RangeTable = new DataTable();
            string sqlQuery = @"[pro_GetTruongDaiHoc]";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Id", id);
                        cmd.Parameters.AddWithValue("@PageSize", pageSize);
                        cmd.Parameters.AddWithValue("@CurrentPage", currentPage).Direction = ParameterDirection.Output;
                        cmd.Parameters.AddWithValue("@TotalPages", totalPages).Direction = ParameterDirection.Output;

                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);

                            student.IndSeq = Convert.ToInt32(dataSet.Tables[0].Rows[0][Fields.Proc.ThuTuNganh]);

                            student.UniTotalRecords = Convert.ToInt32(dataSet.Tables[1].Rows[0][Fields.Proc.HitCounter]);
                            student.IndTotalRecords = Convert.ToInt32(dataSet.Tables[2].Rows[0][Fields.Proc.HitCounter]);
                            student.UniEqualsToMe = Convert.ToInt32(dataSet.Tables[3].Rows[0][Fields.Proc.HitCounter]);
                            student.UniGreaterThanMe = Convert.ToInt32(dataSet.Tables[4].Rows[0][Fields.Proc.HitCounter]);
                            student.IndEqualsToMe = Convert.ToInt32(dataSet.Tables[5].Rows[0][Fields.Proc.HitCounter]);
                            student.IndGreaterThanMe = Convert.ToInt32(dataSet.Tables[6].Rows[0][Fields.Proc.HitCounter]);

                            //if (dataSet.Tables[7].Rows.Count > 0 && dataSet.Tables[7].Rows[0][Fields.Proc.Diem] != DBNull.Value)
                            //{
                            //    student.StdMark = Convert.ToSingle(dataSet.Tables[7].Rows[0][Fields.Proc.Diem]);
                            //}
                            //if (dataSet.Tables[8].Rows.Count > 0 && dataSet.Tables[8].Rows[0][Fields.Proc.ChiTieu] != DBNull.Value)
                            //{
                            //    student.UniPoint = Convert.ToInt32(dataSet.Tables[8].Rows[0][Fields.Proc.ChiTieu]);
                            //}
                            //if (dataSet.Tables[9].Rows.Count > 0 && dataSet.Tables[9].Rows[0][Fields.Proc.ChiTieu] != DBNull.Value)
                            //{
                            //    student.IndPoint = Convert.ToInt32(dataSet.Tables[9].Rows[0][Fields.Proc.ChiTieu]);
                            //}

                            var row = dataSet.Tables[10].Rows[0];

                            student.Id = Convert.ToInt32(row[Fields.ExamResult.Id]);
                            student.UniSeq = Convert.ToInt32(row[Fields.ExamResult.RowId]);
                            student.University = row[Fields.ExamResult.MaTruong].ToString();
                            student.UniversityName = row[Fields.ExamResult.TenTruong].ToString();
                            student.Industry = (row[Fields.ExamResult.MaNganh] == DBNull.Value) ? string.Empty : row[Fields.ExamResult.MaNganh].ToString();
                            student.Group = (row[Fields.ExamResult.KhoiThi] == DBNull.Value) ? string.Empty : row[Fields.ExamResult.KhoiThi].ToString();
                            student.Code = row[Fields.ExamResult.SBD].ToString();
                            student.Name = row[Fields.ExamResult.Hoten].ToString();
                            student.Subject1 = row[Fields.ExamResult.DM1] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM1]) : null;
                            student.Subject2 = row[Fields.ExamResult.DM2] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM2]) : null;
                            student.Subject3 = row[Fields.ExamResult.DM3] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM3]) : null;
                            student.Subject4 = row[Fields.ExamResult.DM4] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM4]) : null;
                            student.Subject5 = row[Fields.ExamResult.DM5] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM5]) : null;
                            student.Subject6 = row[Fields.ExamResult.DM6] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM6]) : null;
                            //student.Location = row[Fields.ExamResult.KhuVuc].ToString();
                            //student.AddMark = GetAddMark(student.Location);
                            student.Total = row[Fields.ExamResult.TongDiem] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.TongDiem]) : null;

                            // student.RangeTable = dataSet.Tables[11];
                        }

                        currentPage = Convert.ToInt32(cmd.Parameters["@CurrentPage"].Value);
                        totalPages = Convert.ToInt32(cmd.Parameters["@TotalPages"].Value);
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return student;
        }

        /// <summary>
        /// Get Danh Sach Thu Hang Theo Thi Sinh
        /// </summary>
        /// <param name="student"></param>
        /// <param name="byUniversity"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="totalPages"></param>
        /// <returns></returns>
        public static DataTable GetStudentDetails(StudentInfo student, bool byUniversity, int pageSize, ref int currentPage, ref int totalPages)
        {
            student.RangeTable = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    string sqlQuery = @"
                            SELECT * 
                            FROM (
                                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * 
                                    FROM ThiSinh_DaiHoc WHERE ID_Truong = @University 
                                            AND (@Industry IS NULL OR ISNULL(MaNganh, '') = @Industry)
                            ) A WHERE RowID BETWEEN @StartIndex AND @EndIndex";
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        int totalRecords;
                        if (byUniversity)
                        {
                            if (currentPage == -1) currentPage = (student.UniSeq - 1) / pageSize;
                            totalRecords = student.UniTotalRecords;
                            totalPages = (int)Math.Ceiling((float)student.UniTotalRecords / pageSize);
                            cmd.Parameters.AddWithValue("@Industry", DBNull.Value);
                        }
                        else
                        {
                            if (currentPage == -1) currentPage = (student.IndSeq - 1) / pageSize;
                            totalRecords = student.IndTotalRecords;
                            totalPages = (int)Math.Ceiling((float)student.IndTotalRecords / pageSize);
                            cmd.Parameters.AddWithValue("@Industry", student.Industry);
                        }

                        int startIndex = currentPage * pageSize + 1;
                        int endIndex = Math.Min(totalRecords, startIndex + pageSize - 1);

                        cmd.Parameters.AddWithValue("@University", student.University);
                        cmd.Parameters.AddWithValue("@StartIndex", startIndex);
                        cmd.Parameters.AddWithValue("@EndIndex", endIndex);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            using (DataSet dataSet = new DataSet())
                            {
                                adapter.Fill(dataSet);
                                student.RangeTable = dataSet.Tables[0];
                            }
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return student.RangeTable;
        }

    }
}
